%% @doc The ErlyDB MySQL driver.

-module(erlydb_mysql).
-export([connect/5,
	 connect/4,
	 q/1,
	 get_metadata/0,
	 select/1,
	 select/2,
	 update/1,
	 get_last_insert_id/0]).


%% We only support one connection (pool) for now
-define(MID, mysql1).

%% Useful for debugging
-define(L(Obj), io:format("LOG ~w ~p\n", [?LINE, Obj])).
-define(S(Obj), io:format("LOG ~w ~s\n", [?LINE, Obj])).

%% @doc Initialize a MySQL connection.
%%
%% @type options() A list of {name::atom(), Value::term()} pairs.
%%   Currently, the only supported option is {num_conns, Num::integer()}
%%
%% NOTE: It looks like due to a bug in the MySQL driver connection pooling,
%%   having more than one connection could interfere with transactions.
%%
%% @spec connect(Hostname::string(), Username::string(), Password::string(),
%%   Database::string(), Options::options()) ->
%%     ok | {error, Error}
%% @throws {error, Error}
connect(Hostname, Username, Password, Database, Options) ->
    NumConns =
	case Options of
	    [] -> 1;
	    [{num_conns, Num}] -> Num;
	    Other -> throw({error, {invalid_options, Other}})
	end,
    mysql:start_link(?MID, Hostname, Username, Password, Database),
    connect2(?MID, Hostname, Username, Password, Database, NumConns-1).

%% Same as above, but with the default options (i.e. 1 connection)
connect(Hostname, Username, Password, Database) ->
    connect(Hostname, Username, Password, Database, []).

connect2(_Mid, _Hostname, _Username, _Password, _Database, 0) ->
    ok;

%% TODO change the port to an option
connect2(Mid, Hostname, Username, Password, Database, Num) ->
    mysql:connect(Mid, Hostname, 3306, Username, Password, Database, true),
    connect2(Mid, Hostname, Username, Password, Database, Num-1).
    

	      

%% @doc Execute a MySQL query directly against the MySQL driver.
%%
%% @spec q(Sql::string()) -> mysql_result()
q(Sql) ->
    mysql:fetch(?MID, Sql).

%% @doc Get the table names and fields for the database.
%%
%% @spec get_metadata() -> dict()
%% @throws {error, Error}
get_metadata() ->
    {data, Res} = q("show tables"),
    Tables = mysql:get_result_rows(Res),
    lists:foldl(
      fun([Table | _], TablesDict) ->
	      case q("describe " ++ Table) of
		  {data, FieldRes} ->
		      Fields = mysql:get_result_rows(FieldRes),
		      FNames =
			  lists:map(
			    fun(Field) -> list_to_atom(lists:nth(1, Field)) end,
			    Fields),
		      dict:store(Table, FNames, TablesDict);
		  {error, _Err} = Res ->
		      throw(Res)
	      end
      end, dict:new(), Tables).


%% @doc Execute a SELECT query and return all the values after
%%   type-converting them to their Erlang equivalents.
%%
%% @spec select(Sql::string()) -> {ok, Rows::list()} | {error, Error}
select(Sql) ->
    select2(Sql, {}).

%% @doc Perform a SELECT query, returning all rows with additional
%%   data to support funcitons generated by ErlyDB in runtime. All values
%%   are type-converted to their Erlang equivalents.
%%
%% @spec(Sql::string(), FixedCols::tuple()) -> {ok, Rows} | {error, Error}
select(Sql, Module) ->
    select2(Sql, {false, Module}).

%% Perform a select query and convert all result values to their
%% corresponding Erlang types.
%%
%% TODO by hacking the MySQL driver, we may be able to
%% optimize away most, if not all of this function.
select2(Sql, FixedCols) ->
    case q(Sql) of
	{data, {mysql_result, Meta, Rows, _Updates, _Something}} ->
	    Converters =
		lists:foldl(
		  fun({_TableName, _ColName, _ColSize, ColType}, Acc) ->
			  erlang:append_element(Acc, get_converter(ColType))
		  end, {}, Meta),
	    Result =
		lists:foldl(
		  fun(Fields, Acc) ->
			  {Row, _Count} = lists:foldl(
			    fun(null, {Rec, Idx}) ->
				    {erlang:append_element(Rec, undefined),
				     Idx+1};
				(Val, {Rec, Idx}) ->
				    NewVal =
					case element(Idx, Converters) of
					    undefined ->
						Val;
					    Conv ->
						Conv(Val)
					end,
				    {erlang:append_element(Rec, NewVal), Idx+1}
			    end, {FixedCols, 1}, Fields),
			  [Row | Acc]
		  end, [], Rows),
	    {ok, lists:reverse(Result)};
	Err ->
	    Err
    end.
    

get_converter(ColType) ->
    case ColType of
	T when T == 'TINY';
	       T == 'SHORT';
	       T == 'LONG';
	       T == 'LONGLONG';
	       T == 'INT24';
	       T == 'YEAR' ->
	    fun list_to_integer/1;
	T when T == 'TIMESTAMP';
	       T == 'DATETIME' ->
	    fun(Val) ->
		    {ok, [Year, Month, Day, Hour, Minute, Second], _Leftovers} =
			io_lib:fread("~d-~d-~d ~d:~d:~d", Val),
		    {{Year, Month, Day}, {Hour, Minute, Second}}
	    end;
	'TIME' ->
	    fun(Val) ->
		    {ok, [Hour, Minute, Second], _Leftovers} =
			io_lib:fread("~d:~d:~d", Val),
		    {Hour, Minute, Second}
	    end;
	'DATE' ->
	    fun(Val) ->
		    {ok, [Year, Month, Day], _Leftovers} =
			io_lib:fread("~d-~d-~d", Val),
		    {Year, Month, Day}
	    end;
	T when T == 'DECIMAL';
	       T == 'FLOAT' ->
	    fun(Val) ->
		    {ok, [Num], _Leftovers} =
			case io_lib:fread("~f", Val) of
			    {error, _} ->
				io_lib:fread("~d", Val);
			    Res ->
				Res
			end,
		    Num
	    end;
	_Other ->
	    undefined
    end.

%% @doc Perform a DELETE or UPDATE statement.
%%
%% @spec update(Sql::string()) -> {ok, NumAffected} | {error, Err} 
update(Sql) ->
    case q(Sql) of
	{updated, MySQLRes} ->
	    %% TODO It appears like the MySQL driver reports
	    %% 0 row updates erroneously
	    {ok, mysql:get_result_affected_rows(MySQLRes)};
	{error, _Err} = Err ->
	    Err
    end.

get_last_insert_id() ->
    case select("SELECT last_insert_id()") of
	{ok, [{Val}]} ->
	    {ok, Val};
	Err ->
	    Err
    end.
	    
    
